## 14.3. Dynamic Database Access

So far we have dealt with applications where you know exactly what needs to be done at compile time. If this were the only kind of database support that JDBC provided, no one could ever write tools like the mysql and msql interactive command line tools that determine SQL calls at runtime and execute them. The JDBC `Statement` class provides the `execute()` method for executing SQL that may be either a query or an update. Additionally, `ResultSet` instances provide runtime information about themselves in the form of an interface called `ResultSetMetaData` which you can access via the `getMetaData()` call in the `ResultSet`.

### 14.3.1. Meta Data

The term meta data sounds officious, but it is really nothing other than extra data about some object that would otherwise waste resources if it were actually kept in the object. For example, simple applications do not need the name of the columns associated with a `ResultSet` -- the programmer probably knew that when the code was written. Embedding this extra information in the `ResultSet` class is thus not considered by JDBC's designers to be core to the functionality of a `ResultSet`. Data such as the column names, however, is very important to some database programmers -- especially those writing dynamic database access. The JDBC designers provide access to this extra information -- the meta data -- via the `ResultSetMetaData` interface. This class specifically provides:

- The number of columns in a result set

- Whether `NULL` is a valid value for a column

- The label to use for a column header

- The name for a given column

- The source table for a given column

- The datatype of a given column

Another example class that comes with the mSQL-JDBC driver is the `Exec` application. It will accept any SQL you specify on a command line and execute it. Example 14-1 shows that source code.

#### Example 14-1. The Source to the Exec Application for Executing Dynamic SQL

>     import java.sql.*;

>     public class Exec {
>         public static void main(String args[]) {
>             Connection con = null;
>             String sql = "";

>             for(int i=0; i<args.length; i++) {
>                 sql = sql + args[i];
>                 if( i < args.length - 1 ) {
>                     sql = sql + " ";
>                 }
>             }
>             System.out.println("Executing: " + sql);
>             try {
>                 Class.forName("com.imaginary.sql.msql.MsqlDriver").newInstance();
>                 String url = "jdbc:msql://athens.imaginary.com:1114/db_test";
>                 con = DriverManager.getConnection(url, "borg", "");
>                 Statement s = con.createStatement();

>                 if( s.execute(sql) ) {
>                     ResultSet r = s.getResultSet();
>                     ResultSetMetaData meta = r.getMetaData();
>                     int cols = meta.getColumnCount();
>                     int rownum = 0;

>                     while( r.next() ) {
>                         rownum++;
>                         System.out.println("Row: " + rownum);
>                         for(int i=0; i<cols; i++) {
>                             System.out.print(meta.getColumnLabel(i+1) + ": "
>                                              + r.getObject(i+1) +  ", ");
>                         }
>                         System.out.println("");
>                     }
>                 }
>                 else {
>                     System.out.println(s.getUpdateCount() + " rows affected.");
>                 }
>                 s.close();
>                 con.close();
>             }
>             catch( Exception e ) {
>                 e.printStackTrace();
>             }
>             finally {
>                 if( con != null ) {
>                     try { con.close(); }
>                     catch( SQLException e ) { }
>                 }
>             }
>         }
>     }

Each result set provides a `ResultSetMetaData` instance via the `getMetaData()` method. In the case of dynamic database access, we need to find out the how many columns are in a result set so that we are certain to retrieve each column as well as the names of each of the columns for display to the user. The meta data for our result set provides all of this information via the `getColumnCount()` and `getColumnLabel()` methods.

### 14.3.2. Processing Dynamic SQL

The concept introduced in Example 14-1 is the dynamic SQL call. Because we do not know whether we will be processing a query or an update, we need to pass the SQL call through the `execute()` method. This method returns `true` if the statement returned a result set or `false` if none was produced. In the example, if it returns `true`, the application gets the returned `ResultSet` through a call to `getResultSet()` . The application can then go on to do normal result set processing. If, on the other hand, the statement performed some sort of database modification, you can call `getUpdateCount()` to find out how many rows were modified by the statement.

Copyright © 2001 O'Reilly & Associates. All rights reserved.